{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T10:44:16.155121Z",
     "iopub.status.busy": "2023-11-07T10:44:16.154914Z",
     "iopub.status.idle": "2023-11-07T10:44:17.021617Z",
     "shell.execute_reply": "2023-11-07T10:44:17.021027Z",
     "shell.execute_reply.started": "2023-11-07T10:44:16.155094Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "\n",
    "stage = 'A'\n",
    "\n",
    "df_train = pd.read_csv('../../../contest/train/GSLD_MB_TRNFLW.csv')\n",
    "df_test = pd.read_csv('../../../contest/A/GSLD_MB_TRNFLW_A.csv')\n",
    "\n",
    "save_path = '../data'\n",
    "\n",
    "end_date_train = datetime(1996,7,5)\n",
    "end_date_test = datetime(1996,8,4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T10:44:17.022964Z",
     "iopub.status.busy": "2023-11-07T10:44:17.022767Z",
     "iopub.status.idle": "2023-11-07T10:44:17.034954Z",
     "shell.execute_reply": "2023-11-07T10:44:17.034363Z",
     "shell.execute_reply.started": "2023-11-07T10:44:17.022939Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# trn_code_list = df_train.TRANSCODE.value_counts(ascending=False).index\n",
    "# trn_code_dic = {trn_code_list[i]:i for i,_ in enumerate(trn_code_list)}\n",
    "def prepro(df, end_date, offset):\n",
    "    df.AMOUNT.fillna(df.AMOUNT.mean())\n",
    "    df.AMOUNT = df.AMOUNT.apply(lambda x: round(pow(x/3.12,3),2))\n",
    "#     df.TRANSCODE = df.TRANSCODE.map(trn_code_dic)\n",
    "    \n",
    "    df['DATE'] = pd.to_datetime(df['DATE'], format='%Y%m%d')\n",
    "    df['weekday'] = (df['DATE'].dt.dayofweek+7+offset) % 7 # 当周星期几 \n",
    "    \n",
    "    tmp_df = df.groupby('CUST_NO').agg(\n",
    "        trnflw_trn_cd_nunique = ('TRANSCODE', 'nunique'),\n",
    "        trnflw_last_date = ('DATE', 'max'),\n",
    "        trnflw_mean_amount = ('AMOUNT', 'mean'),\n",
    "        trnflw_sum_amount = ('AMOUNT', 'sum'),\n",
    "        trnflw_exist_weekend = ('weekday', 'max'), # 交易记录是否存在周末\n",
    "        trnflw_cust_cnt = ('weekday', 'count') # 总条数\n",
    "    ).reset_index()\n",
    "    \n",
    "    tmp_df['trnflw_last_date'] = pd.to_datetime(tmp_df['trnflw_last_date'], format='%Y%m%d')\n",
    "    tmp_df['trnflw_weekday'] = (tmp_df['trnflw_last_date'].dt.dayofweek+7+offset)% 7 # 当周星期几\n",
    "    tmp_df['trnflw_last_date'] = (end_date - tmp_df['trnflw_last_date']).dt.days\n",
    "#     tmp_df['trnflw_is_weekend'] = tmp_df['trnflw_weekday'].apply(lambda x: 1 if x==5 or x==6 else 0) # 最大交易日期是否是周末\n",
    "    tmp_df['trnflw_exist_weekend'] = tmp_df['trnflw_exist_weekend'].apply(lambda x: 1 if x==6 or x==5 else 0) # 交易记录是否存在周末\n",
    "    \n",
    "    # 计算周末交易的条数\n",
    "    tmp_df2 = df[((df.weekday==5)|(df.weekday==6))].groupby('CUST_NO').agg(\n",
    "        trnflw_weekend_cnt = ('weekday', 'count')\n",
    "    ).reset_index()\n",
    "    \n",
    "    tmp_df = tmp_df.merge(tmp_df2, how='left', on='CUST_NO')\n",
    "    tmp_df['trnflw_weekend_cnt'] = tmp_df.trnflw_weekend_cnt.fillna(0)\n",
    "    tmp_df['trnflw_weekend_ratio'] = tmp_df.trnflw_weekend_cnt / tmp_df.trnflw_cust_cnt # 周末交易的比率\n",
    "    \n",
    "    del tmp_df['trnflw_weekday']\n",
    "\n",
    "    return tmp_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T10:44:17.036182Z",
     "iopub.status.busy": "2023-11-07T10:44:17.036000Z",
     "iopub.status.idle": "2023-11-07T10:44:18.576131Z",
     "shell.execute_reply": "2023-11-07T10:44:18.575548Z",
     "shell.execute_reply.started": "2023-11-07T10:44:17.036159Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_train = prepro(df_train, end_date_train, 0)\n",
    "df_test = prepro(df_test, end_date_test, -3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T10:44:18.577364Z",
     "iopub.status.busy": "2023-11-07T10:44:18.577154Z",
     "iopub.status.idle": "2023-11-07T10:44:18.615911Z",
     "shell.execute_reply": "2023-11-07T10:44:18.615453Z",
     "shell.execute_reply.started": "2023-11-07T10:44:18.577337Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>trnflw_trn_cd_nunique</th>\n",
       "      <td>3914.0</td>\n",
       "      <td>1.486203</td>\n",
       "      <td>0.716666</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>5.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_last_date</th>\n",
       "      <td>3914.0</td>\n",
       "      <td>17.412877</td>\n",
       "      <td>20.178799</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.0000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>8.800000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_mean_amount</th>\n",
       "      <td>3914.0</td>\n",
       "      <td>11893.519185</td>\n",
       "      <td>29941.623448</td>\n",
       "      <td>0.01</td>\n",
       "      <td>1500.4300</td>\n",
       "      <td>3982.278333</td>\n",
       "      <td>10216.500048</td>\n",
       "      <td>8.997437e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_sum_amount</th>\n",
       "      <td>3914.0</td>\n",
       "      <td>116642.928983</td>\n",
       "      <td>483278.919268</td>\n",
       "      <td>0.01</td>\n",
       "      <td>6998.3325</td>\n",
       "      <td>25523.515000</td>\n",
       "      <td>77177.162500</td>\n",
       "      <td>1.440589e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_exist_weekend</th>\n",
       "      <td>3914.0</td>\n",
       "      <td>0.714870</td>\n",
       "      <td>0.451534</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_cust_cnt</th>\n",
       "      <td>3914.0</td>\n",
       "      <td>9.710271</td>\n",
       "      <td>15.655728</td>\n",
       "      <td>1.00</td>\n",
       "      <td>3.0000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>4.760000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_weekend_cnt</th>\n",
       "      <td>3914.0</td>\n",
       "      <td>2.621615</td>\n",
       "      <td>5.007753</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.680000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_weekend_ratio</th>\n",
       "      <td>3914.0</td>\n",
       "      <td>0.263432</td>\n",
       "      <td>0.252424</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.393281</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        count           mean            std   min        25%  \\\n",
       "trnflw_trn_cd_nunique  3914.0       1.486203       0.716666  1.00     1.0000   \n",
       "trnflw_last_date       3914.0      17.412877      20.178799  0.00     2.0000   \n",
       "trnflw_mean_amount     3914.0   11893.519185   29941.623448  0.01  1500.4300   \n",
       "trnflw_sum_amount      3914.0  116642.928983  483278.919268  0.01  6998.3325   \n",
       "trnflw_exist_weekend   3914.0       0.714870       0.451534  0.00     0.0000   \n",
       "trnflw_cust_cnt        3914.0       9.710271      15.655728  1.00     3.0000   \n",
       "trnflw_weekend_cnt     3914.0       2.621615       5.007753  0.00     0.0000   \n",
       "trnflw_weekend_ratio   3914.0       0.263432       0.252424  0.00     0.0000   \n",
       "\n",
       "                                50%           75%           max  \n",
       "trnflw_trn_cd_nunique      1.000000      2.000000  5.000000e+00  \n",
       "trnflw_last_date          10.000000     23.000000  8.800000e+01  \n",
       "trnflw_mean_amount      3982.278333  10216.500048  8.997437e+05  \n",
       "trnflw_sum_amount      25523.515000  77177.162500  1.440589e+07  \n",
       "trnflw_exist_weekend       1.000000      1.000000  1.000000e+00  \n",
       "trnflw_cust_cnt            6.000000     11.000000  4.760000e+02  \n",
       "trnflw_weekend_cnt         1.000000      3.000000  1.680000e+02  \n",
       "trnflw_weekend_ratio       0.250000      0.393281  1.000000e+00  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test.describe().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T10:44:18.616846Z",
     "iopub.status.busy": "2023-11-07T10:44:18.616676Z",
     "iopub.status.idle": "2023-11-07T10:44:18.662998Z",
     "shell.execute_reply": "2023-11-07T10:44:18.662550Z",
     "shell.execute_reply.started": "2023-11-07T10:44:18.616822Z"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>trnflw_trn_cd_nunique</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>1.492887</td>\n",
       "      <td>0.730205</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>6.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_last_date</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>17.694573</td>\n",
       "      <td>19.642890</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>24.000000</td>\n",
       "      <td>8.900000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_mean_amount</th>\n",
       "      <td>47096.0</td>\n",
       "      <td>11648.391780</td>\n",
       "      <td>32657.752974</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1460.2878</td>\n",
       "      <td>3899.412857</td>\n",
       "      <td>9996.854583</td>\n",
       "      <td>2.174533e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_sum_amount</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>116809.363549</td>\n",
       "      <td>846578.277559</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7180.0675</td>\n",
       "      <td>25143.365000</td>\n",
       "      <td>74037.920000</td>\n",
       "      <td>9.136525e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_exist_weekend</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>0.645356</td>\n",
       "      <td>0.478410</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_cust_cnt</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>9.772708</td>\n",
       "      <td>15.964699</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>11.000000</td>\n",
       "      <td>7.810000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_weekend_cnt</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>2.203533</td>\n",
       "      <td>4.381530</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.970000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trnflw_weekend_ratio</th>\n",
       "      <td>47098.0</td>\n",
       "      <td>0.214488</td>\n",
       "      <td>0.236797</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         count           mean            std  min        25%  \\\n",
       "trnflw_trn_cd_nunique  47098.0       1.492887       0.730205  1.0     1.0000   \n",
       "trnflw_last_date       47098.0      17.694573      19.642890  0.0     3.0000   \n",
       "trnflw_mean_amount     47096.0   11648.391780   32657.752974  0.0  1460.2878   \n",
       "trnflw_sum_amount      47098.0  116809.363549  846578.277559  0.0  7180.0675   \n",
       "trnflw_exist_weekend   47098.0       0.645356       0.478410  0.0     0.0000   \n",
       "trnflw_cust_cnt        47098.0       9.772708      15.964699  1.0     3.0000   \n",
       "trnflw_weekend_cnt     47098.0       2.203533       4.381530  0.0     0.0000   \n",
       "trnflw_weekend_ratio   47098.0       0.214488       0.236797  0.0     0.0000   \n",
       "\n",
       "                                50%           75%           max  \n",
       "trnflw_trn_cd_nunique      1.000000      2.000000  6.000000e+00  \n",
       "trnflw_last_date          11.000000     24.000000  8.900000e+01  \n",
       "trnflw_mean_amount      3899.412857   9996.854583  2.174533e+06  \n",
       "trnflw_sum_amount      25143.365000  74037.920000  9.136525e+07  \n",
       "trnflw_exist_weekend       1.000000      1.000000  1.000000e+00  \n",
       "trnflw_cust_cnt            6.000000     11.000000  7.810000e+02  \n",
       "trnflw_weekend_cnt         1.000000      3.000000  1.970000e+02  \n",
       "trnflw_weekend_ratio       0.166667      0.333333  1.000000e+00  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train.describe().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-11-07T10:44:18.663908Z",
     "iopub.status.busy": "2023-11-07T10:44:18.663740Z",
     "iopub.status.idle": "2023-11-07T10:44:19.096122Z",
     "shell.execute_reply": "2023-11-07T10:44:19.095552Z",
     "shell.execute_reply.started": "2023-11-07T10:44:18.663886Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_train.to_csv(save_path+'/GSLD_MB_TRNFLW.csv', index=False)\n",
    "df_test.to_csv(save_path+'/GSLD_MB_TRNFLW_A.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
